﻿using System;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
using VBA = Microsoft.Vbe.Interop;
using Microsoft.Vbe.Interop;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Word;
using System.IO;

namespace Utilities
{
    public static class Macro
    {
        #region "Private Methods"
        public static bool CheckPassWord(string fileName)
        {
            return true;
        }
        public static void Open(string fileName)
        {
            var excel = new Excel.Application();
            var workbook = excel.Workbooks.Open(fileName, false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, false, false, Type.Missing, false, true, Type.Missing);
            //workbook.Application.VBE.ActiveVBProject.VBComponents.Import(fileName);                     
            var project = workbook.VBProject;
            var projectName = project.Name;
            var procedureType = Microsoft.Vbe.Interop.vbext_ProcKind.vbext_pk_Proc;

            foreach (var component in project.VBComponents)
            {
                VBA.VBComponent vbComponent = component as VBA.VBComponent;
                if (vbComponent != null)
                {
                    string componentName = vbComponent.Name;
                    var componentCode = vbComponent.CodeModule;
                    int componentCodeLines = componentCode.CountOfLines;
                    //VBComponent e = project.VBComponents.Item(i);
                    int line = 1;
                    while (line < componentCodeLines)
                    {
                        string procedureName = componentCode.get_ProcOfLine(line, out procedureType);
                        if (procedureName != string.Empty)
                        {
                            int procedureLines = componentCode.get_ProcCountLines(procedureName, procedureType);
                            int procedureStartLine = componentCode.get_ProcStartLine(procedureName, procedureType);
                            int codeStartLine = componentCode.get_ProcBodyLine(procedureName, procedureType);
                            string comments = "[No comments]";
                            if (codeStartLine != procedureStartLine)
                            {
                                comments = componentCode.get_Lines(line, codeStartLine - procedureStartLine);
                            }
                            int signatureLines = 1;
                            while (componentCode.get_Lines(codeStartLine, signatureLines).EndsWith("_"))
                            {
                                signatureLines++;
                            }
                            string signature = componentCode.get_Lines(codeStartLine, signatureLines);
                            signature = signature.Replace("\n", string.Empty);
                            signature = signature.Replace("\r", string.Empty);
                            signature = signature.Replace("_", string.Empty);
                            line += procedureLines - 1;
                        }
                        line++;
                    }
                }
            }
            excel.Quit();
        }
        public static void DeleteMacroOfWordFile(string Files, string saveAsFile, ref bool returnvalues, string PassExcel, string appPath)
        {
            LogService log = new LogService(appPath);
            object objTypeMissing = Type.Missing;
            object filePath = Files;
            Microsoft.Office.Interop.Word.Application wordAppl = new Microsoft.Office.Interop.Word.Application();
            Microsoft.Office.Interop.Word.Document doc = null;
            try
            {
                doc = wordAppl.Documents.Open(ref filePath, ref objTypeMissing,
                   ref objTypeMissing, ref objTypeMissing, ref objTypeMissing,
                   ref objTypeMissing, ref objTypeMissing, ref objTypeMissing,
                   ref objTypeMissing, ref objTypeMissing, ref objTypeMissing,
                   ref objTypeMissing, ref objTypeMissing, ref objTypeMissing,
                   ref objTypeMissing, ref objTypeMissing);

                VBProject project = doc.VBProject;

                for (int i = project.VBComponents.Count; i >= 1; i--)
                {
                    VBComponent component = project.VBComponents.Item(i);
                    try
                    {
                        string test = component.Name;
                        project.VBComponents.Remove(component);
                    }
                    catch (ArgumentException)
                    {
                        continue;
                    }
                }
                object misValue = System.Reflection.Missing.Value;
                if (File.Exists(saveAsFile))
                {
                    File.Delete(saveAsFile);
                }
                log.WriteToLog(string.Format(@" {0} Delete Macro Of Word ByService", DateTime.Now), LogMessageType.Infomation);
                doc.SaveAs(saveAsFile, ref objTypeMissing, ref objTypeMissing, ref objTypeMissing);
                doc.Close(ref objTypeMissing, ref objTypeMissing, ref objTypeMissing);
                File.Delete(Files);
                returnvalues = true;
            }
            catch (Exception ex)
            {
                returnvalues = false;
                log.WriteToLog(string.Format(@" {0} Delete Macro Of Word {1}", DateTime.Now, ex.Message), LogMessageType.Error);
                wordAppl.Quit();
            }
            finally
            {
                try
                {
                    log = null;
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(doc);
                    wordAppl.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(wordAppl);
                    GC.Collect();
                }
                catch
                { }
                finally
                {
                    doc = null;
                    wordAppl = null;
                    if (File.Exists(Files))
                    {
                        File.Delete(Files);
                    }
                }
            }
        }
        public static void DeleteVBMacroByService(string Files, string saveAsFile, ref bool returnvalues, string PassExcel, string appPath)
        {
            LogService log = new LogService(appPath);
            Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); ;
            ExcelApp.DisplayAlerts = false;
            Workbook workbook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
            //workbook = Files;
            bool IsPass = workbook.HasPassword;
            try
            {
                if (!IsPass)
                {
                    ExcelApp.DisplayAlerts = false;
                    workbook = ExcelApp.Workbooks.Open(Files, false, true, Type.Missing, PassExcel, PassExcel, true, Type.Missing, Type.Missing, false, false, Type.Missing, false, true, Type.Missing);
                }
                else
                {
                    workbook = ExcelApp.Workbooks.Open(Files, false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, false, false, Type.Missing, false, true, Type.Missing);
                }
                VBProject project = workbook.VBProject;
                for (int i = project.VBComponents.Count; i >= 1; i--)
                {
                    VBComponent component = project.VBComponents.Item(i);
                    try
                    {
                        string test = component.Name;
                        project.VBComponents.Remove(component);
                    }
                    catch (ArgumentException)
                    {
                        continue;
                    }
                }
                for (int i = project.VBComponents.Count; i >= 1; i--)
                {
                    VBComponent component = project.VBComponents.Item(i);
                    string temp = component.Name;
                    component.CodeModule.DeleteLines(1, component.CodeModule.CountOfLines);
                }
                object misValue = System.Reflection.Missing.Value;
                if (File.Exists(saveAsFile))
                {
                    File.Delete(saveAsFile);
                }
                log.WriteToLog(string.Format(@" {0} Before Saves as", DateTime.Now), LogMessageType.Infomation);
                workbook.SaveAs(saveAsFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                File.Delete(Files);
                returnvalues = true;
            }
            catch (Exception ex)
            {
                returnvalues = false;
                log.WriteToLog(string.Format(@" {0} DeleteVBMacro ByService {1}", DateTime.Now, ex.Message), LogMessageType.Error);
                ExcelApp.Quit();
            }
            finally
            {
                try
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                    ExcelApp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
                    GC.Collect();
                }
                catch
                { }
                finally
                {
                    log = null;
                    workbook = null;
                    ExcelApp = null;
                    if (File.Exists(Files))
                    {
                        File.Delete(Files);
                    }
                }
            }
        }
        public static void Create()
        {
            Excel.Application oXL = null;
            Excel._Workbook oWB = null;
            Excel._Worksheet oSheet = null;
            Excel.Range oRng = null;
            _VBComponent oModule = null; // VBA Module            
            try
            {
                //Start Microsoft.Office.Interop.Excel and get Application object.
                oXL = new Microsoft.Office.Interop.Excel.Application();
                oXL.Visible = true;
                //Get a new workbook.
                oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
                oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
                //Add table headers going cell by cell.
                oSheet.Cells[1, 1] = "First Name";
                oSheet.Cells[1, 2] = "Salary(Read Only)";
                //Format A1:D1 as bold, vertical alignment = center.
                oSheet.get_Range("A1", "B1").Font.Bold = true;
                oSheet.get_Range("A1", "B1").VerticalAlignment =
                Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                // Create an array to multiple values at once.                
                string[,] saNames = new string[5, 2];
                saNames[0, 0] = "Naveen";
                saNames[0, 1] = "10000";
                saNames[1, 0] = "Girish";
                saNames[1, 1] = "20000";
                saNames[2, 0] = "Nandish";
                saNames[2, 1] = "15000";
                saNames[3, 0] = "Rashmi";
                saNames[3, 1] = "40000";
                //Fill A2:B6 with an array of values (First and Salary).
                oSheet.get_Range("A2", "B6").Value2 = saNames;
                //AutoFit columns A:D.
                oRng = oSheet.get_Range("A1", "B1");
                oRng.EntireColumn.AutoFit();
                //Create macro in excel
                CreateMacro(oWB, oModule);
                // Run VBA macro "MakeCellReadOnly" on a range                
                string oRange = "B2:B6";
                LockRange(oWB, oRange);
                //For single cell you can use the function as follows                
                //oRange = "C7";                
                //MakeCellReadOnly(oWB, oRange);
                //Delete the created Macro
                DeleteMacro(oWB, oModule);
                //Make sure Microsoft.Office.Interop.Excel is visible and give the user control                
                //of Microsoft Microsoft.Office.Interop.Excel's lifetime.
                oXL.Visible = true;
                oXL.UserControl = true;
            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);
            }

        }
        private static void DeleteMacro(Microsoft.Office.Interop.Excel._Workbook oWB, _VBComponent oModule)
        {
            Object oMissing = System.Reflection.Missing.Value;
            oWB.Application.Run("DeleteThisModule"
            , oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
            oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
            oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
            oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
        }
        private static void LockRange(Excel._Workbook oWB, string oRange)
        {
            Object oMissing = System.Reflection.Missing.Value;
            //The following command executes the macro by passing the range to the macro
            oWB.Application.Run("LockRange", oRange, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
        }
        private static void CreateMacro(Excel._Workbook oWB, _VBComponent oModule)
        {
            //Create a macro
            oModule = oWB.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
            //Create macro for locking the cell and delete the module            
            string sCode =
            "Sub LockRange(oRange As String)\r\n" +
            "Range(oRange).Select\r\n" +
            "With Selection.Validation \r\n" +
            ".Delete\r\n" +
            ".Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _\r\n" +
            "xlBetween, Formula1:=\"\"\"\"\"\"\r\n" +
            ".ErrorTitle = \"Locked Cell\"\r\n" +
            ".ErrorMessage = \"This cell is locked for modification.\"\r\n" +
            ".ShowError = True\r\n" +
            "End With\r\n" +
            "End Sub\r\n" +
            "Sub DeleteThisModule()\r\n" +
            "Dim vbCom As Object\r\n" +
            "Set vbCom = Application.VBE.ActiveVBProject.VBComponents\r\n" +
            "vbCom.Remove VBComponent:= vbCom.Item(\"Module1\")\r\n" +
            "End Sub\r\n";
            //Add the macro in excel workbook that we have created
            oModule.CodeModule.AddFromString(sCode);
        }
        #endregion
    }
}
